package com.googlecode.mysqlcommenter;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collections;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;
import javax.swing.event.TreeExpansionEvent;
import javax.swing.event.TreeWillExpandListener;
import javax.swing.tree.ExpandVetoException;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import com.googlecode.mysqlcommenter.model.AbstractDbItem;
import com.googlecode.mysqlcommenter.model.Column;
import com.googlecode.mysqlcommenter.model.Db;
import com.googlecode.mysqlcommenter.model.Table;

public class DBConnection implements TreeWillExpandListener {
   private DataSource dataSource;
   private final String host;
   private final String port;
   private final String user;


   public DBConnection(String host, String port, String user, String pw) {
      this.host = host;
      this.port = port;
      this.user = user;
      String url = "jdbc:mysql://"+host+":"+port+"/?useUnicode=true&characterEncoding=UTF-8";
      String driver = "com.mysql.jdbc.Driver";
//      dataSource = new DriverManagerDataSource("com.mysql.jdbc.Driver","jdbc:mysql://"+host+":"+port+"/?useUnicode=true&characterEncoding=UTF-8",user,pw);
      BasicDataSource basicDataSource = new BasicDataSource();
      basicDataSource.setDriverClassName(driver);
      basicDataSource.setUrl(url);
      basicDataSource.setUsername(user);
      basicDataSource.setPassword(pw);
      dataSource=basicDataSource;
   }



   @Override
   public String toString() {
      return user+"@"+host+":"+port;
   }



   public List<String> getDatabases() {

      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      return jdbcTemplate.query("SHOW DATABASES", new RowMapper(){

         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            return rs.getString(1);
         }

      });

   }

   public void treeWillCollapse(TreeExpansionEvent event)
         throws ExpandVetoException {
      // TODO Auto-generated method stub

   }

   public void treeWillExpand(TreeExpansionEvent event)
         throws ExpandVetoException {

      if (event.getPath().getLastPathComponent() instanceof Db){
            addTables((Db)event.getPath().getLastPathComponent());
      }
      if (event.getPath().getLastPathComponent() instanceof Table){
         addColumns((Table)event.getPath().getLastPathComponent());
      }


   }

   private void addColumns(final Table table) {
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      jdbcTemplate.query("SHOW FULL COLUMNS FROM "+table+" FROM "+table.getDb(),new RowMapper(){

         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Column column = new Column(rs.getString(1),table);
            table.getColumns().add(column);
            column.setComment(rs.getString("Comment"));
            column.setDefaultValue(rs.getString("Default"));
            column.setExtra(rs.getString("Extra"));
            column.setKey(rs.getString("Key"));
            column.setType(rs.getString("Type"));
            column.setNullable(rs.getBoolean("Null"));
            return column;
         }

      });
   }

   private void addTables(final Db db) {
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      jdbcTemplate.query("SHOW TABLE STATUS FROM "+db, new RowMapper(){

         public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
            Table table = new Table(rs.getString("Name"),db);
            table.setComment(rs.getString("Comment").split(";")[0]);
            db.getTables().add(table);
            return table;
         }

      });

   }

   public String getComment(AbstractDbItem dbItem)  {
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      if (dbItem instanceof Table){
         final Table table = (Table) dbItem;
         List<String> result =jdbcTemplate.query("SHOW TABLE STATUS FROM "+dbItem.getDb()+" WHERE NAME = ?",new Object[]{table.toString()}, new RowMapper(){

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
               return rs.getString("Comment").split(";")[0];
            }
         });
         return result.get(0);
      }
      if (dbItem instanceof Column){
         Column column = (Column) dbItem;
         List<String> result =jdbcTemplate.query("SHOW FULL COLUMNS FROM "+column.getTable()+" FROM "+dbItem.getDb()+" WHERE Field =?",new Object[]{column.toString()}, new RowMapper(){

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
               return rs.getString("Comment");
            }
         });
         return result.get(0);
      }
      return "";

   }

   public List<Map<String,String>> getSampleData(AbstractDbItem dbItem){
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      if (dbItem instanceof Table){
         final Table table = (Table) dbItem;
         return jdbcTemplate.queryForList("SELECT * FROM "+dbItem.getDb()+"."+table.getName()+" LIMIT 10");
      }
      if (dbItem instanceof Column){
         Column column = (Column) dbItem;
         return jdbcTemplate.queryForList("SELECT "+column.getName()+" FROM "+dbItem.getDb()+"."+column.getTable().getName()+" LIMIT 10");
      }
      return Collections.emptyList();

   }

   public void updateComment(AbstractDbItem dbItem, String text) {
      JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
      if (dbItem instanceof Table){
         Table table = (Table) dbItem;
         jdbcTemplate.update("ALTER TABLE "+table.getDb()+"."+table+" COMMENT=?", new Object[]{text});
      }
      if (dbItem instanceof Column){
         Column column = (Column) dbItem;

         List<String[]> result =jdbcTemplate.query("SHOW FULL COLUMNS FROM "+column.getTable()+" FROM "+dbItem.getDb()+" WHERE Field =?",new Object[]{column.toString()}, new RowMapper(){

            public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
               return new String[] {rs.getString("Type"),rs.getString("Extra"),rs.getString("Null"),rs.getString("Default")};
            }
         });

         String type = result.get(0)[0];
         String extra = result.get(0)[1];
         String notnull = "NO".equalsIgnoreCase(result.get(0)[2])?" NOT NULL ":"";
         String defaultval = result.get(0)[3]==null?"":" DEFAULT '"+result.get(0)[3]+"' ";
         jdbcTemplate.update("ALTER TABLE "+column.getDb()+"."+column.getTable()+" MODIFY COLUMN "+column+" "+type +" "+notnull+defaultval+extra+" COMMENT ?", new Object[]{text});

      }

   }

   public Db getDb(final AbstractDbItem dbItem) throws Exception {
      if(dbItem==null ||dbItem.getDb()==null){
         throw new RuntimeException("no database selected");
      }
      Db db = new Db(dbItem.getDb().getName());
      addTables(db);
      for (Table table : db.getTables()) {
         addColumns(table);
      }
      return db;

   }



}
